package com.example.server.mapper;


import com.example.server.pojo.CityLevel;
import com.example.server.pojo.Line;
import com.example.server.pojo.Income;
import com.example.server.pojo.Education;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface Mapper1 {

    @Select("select age from new_data group by age order by age")
    List<String> getAgeList();
    @Select("select" +
            "            sum(case when is_married = '已婚' then 1 else 0 end)/count(*)*100 as 已婚率" +
            "        FROM new_data" +
            "        where ${name} = #{edu}" +
            "        GROUP BY age" +
            "        ORDER BY age")
    List<Double> getByEducation(String name,String edu);
//    @Select("SELECT\n" +
//            "\t\teducation,\n" +
//            "\t\tCOUNT(*)/(SELECT COUNT(*) FROM new_data WHERE is_married='已婚' and sex='female' and age='26~35') as ratio\n" +
//            "\tFROM new_data\n" +
//            "\tWHERE is_married='已婚' and sex=#{sex} and age='26~35'\n" +
//            "\tGROUP BY education\n" +
//            "\tORDER BY ratio desc")
//    List<Education> getEducationZhanbiIn25(String sex);
    @Select("SELECT\n" +
            "\t\teducation,\n" +
            "\t\tCOUNT(*)/(SELECT COUNT(*) FROM new_data WHERE is_married='已婚' and sex='female' and age='26~35') as ratio\n" +
            "\tFROM new_data\n" +
            "\tWHERE is_married='已婚' and age='26~35'\n" +
            "\tGROUP BY education\n" +
            "\tORDER BY ratio desc")
    List<Education> getEducationRatioIn25();
    @Select("SELECT\n" +
            "\t\tcity_level,\n" +
            "\t\tCOUNT(*)/(SELECT COUNT(*) FROM new_data WHERE is_married='已婚' and age='26~35') as ratio\n" +
            "\tFROM new_data\n" +
            "\tWHERE is_married='已婚' and age='26~35'\n" +
            "\tGROUP BY city_level\n" +
            "\tORDER BY ratio desc")
    List<CityLevel> getCityLevelRatioIn25();

    @Select("SELECT\n" +
            "\t\tincome,\n" +
            "\t\tCOUNT(*)/(SELECT COUNT(*) FROM new_data WHERE is_married='已婚' and age='26~35') as ratio\n" +
            "\tFROM new_data\n" +
            "\tWHERE is_married='已婚' and age='26~35'\n" +
            "\tGROUP BY income\n" +
            "\tORDER BY ratio desc")
    List<Income> getIncomeRatioIn25();
}
